package com.monas.server.database;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.monas.server.MonasException;
import com.monas.server.MonasProperties;
import com.monas.server.parser.MonasHeader;

public class MonasDatabase {

	private static final String USER = "monas_user";

	private static final String PASSWORD = "monas_pwd";

	private static final String HOSTNAME = "monas_host";

	private static final String DB_NAME = "monas_dbname";

	private static final String DRIVER = "org.postgresql.Driver";

	private Connection connection = null;

	private static MonasDatabase database = null;

	private MonasDatabase(String username, String password, String host,
			String database) throws SQLException, ClassNotFoundException {
		// Driver identification
		Class.forName(DRIVER);

		String url = "jdbc:postgresql://" + host + "/" + database;
		connection = DriverManager.getConnection(url, username, password);
	}

	public static MonasDatabase getInstance() {
		if (database == null) {
			try {
				String userName = MonasProperties.getSystemProperty(USER);
				String password = MonasProperties.getSystemProperty(PASSWORD);
				String hostName = MonasProperties.getSystemProperty(HOSTNAME);
				String databaseName = MonasProperties
						.getSystemProperty(DB_NAME);
				database = new MonasDatabase(userName, password, hostName,
						databaseName);
			} catch (Exception e) {
				System.out.println("Problem creating database: "
						+ e.getMessage());
			}
		}
		return database;
	}

	public List<Object[]> executeQuery(String query) {
		ResultSet rs = null;
		PreparedStatement statement = null;
		LinkedList<Object[]> list = new LinkedList<Object[]>();
		try {
			statement = connection.prepareStatement(query);
			rs = statement.executeQuery();

			ResultSetMetaData metaData = rs.getMetaData();

			while (rs.next()) {
				Object[] fields = new Object[metaData.getColumnCount()];
				for (int i = 0; i < fields.length; i++) {
					fields[i] = rs.getObject(i + 1);
				}
				list.addLast(fields);
			}
		} catch (Exception e) {
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}

				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				System.out.println("Problem executing query : " + query + " : "
						+ e.getMessage());
			}
		}
		return list;
	}

	public String getProperty(String query) {
		List<Object[]> list = executeQuery(query);

		if (list.size() == 0) {
			return null;
		}

		return (String) list.get(0)[0];
	}

	public void insertTable(String tableName, String fileName,
			MonasHeader[] headers, List<Object[]> records) throws SQLException {
		String sql = "INSERT INTO " + tableName + " VALUES (?, ?";
		for (int i = 0; i < headers.length; i++) {
			sql += ", ?";
		}
		sql += ")";

		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			String[] names = splitName(fileName);

			// Set node
			statement.setString(1, names[0]);
			// Set time
			statement.setTimestamp(2, new Timestamp(getLong(names)));

			for (Object[] fields : records) {

				for (int i = 0; i < fields.length; i++) {
					statement.setObject(i + 3, fields[i],
							headers[i].getSqlType());
				}

				statement.addBatch();
			}

			checkUpdateCounts(statement.executeBatch());
		} catch (BatchUpdateException e) {
			int[] updateCounts = e.getUpdateCounts();
			checkUpdateCounts(updateCounts);
			try {
				connection.rollback();
			} catch (Exception e2) {
				e.printStackTrace();
			}
		} finally {
			if (statement != null) {
				statement.close();
			}
		}

	}

	private long getLong(String[] names) {
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss");

		try {
			Date fileDate = dateFormat.parse(names[2] + "_" + names[3]);
			return fileDate.getTime();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return 0;
	}

	private String[] splitName(String fileName) {
		int dotIdx = fileName.indexOf(".");
		return fileName.substring(0, dotIdx).split("\\_");
	}

	private void checkUpdateCounts(int[] updateCounts) {
		for (int i = 0; i < updateCounts.length; i++) {
			if (updateCounts[i] >= 0) {
				System.out.println("Successfully executed; updateCount="
						+ updateCounts[i]);
			} else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
				System.out
						.println("Successfully executed; updateCount=Statement.SUCCESS_NO_INFO");
			} else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
				System.out
						.println("Failed to execute; updateCount=Statement.EXECUTE_FAILED");
			}
		}
	}

	public void dropTable(String tableName) throws SQLException {
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement("DROP TABLE IF EXISTS "
					+ tableName);
			statement.execute();
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void close() {
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				System.out.println(new MonasException(
						"Database cannot closed because " + e.getMessage())
						.getMessage());
			}
		}
	}

}
